#-*-coding:utf-8-*-
# 作者    ：SunDuWei
# 创作时间 ：2020-05-14
'''
DB_opreate用于python操作数据库进行一下操作
1.数据入库
2.读取数据
'''

import sqlite3
import pandas
from future_data_L1 import *


class Work_DB:
    def __init__(self, DB_name, Table_name):
        self.DB_name = DB_name
        self.Table_name = Table_name

    def table_establish(self, conn, Coloumns_dict):
        '''

        :param Coloumns_dict: 已经生成好的columns对应的type
        :param cursor: 连接数据的游标cursor
        :return:
        '''
        # 生成创建表的sql语句
        sql_replace = ''
        j = 1
        for keys, values in Coloumns_dict.items():
            if keys == 'primary_key' and j != len(Coloumns_dict):
                j += 1
                continue
            sql_replace = sql_replace + keys + ' ' + values + ','
            if j == len(Coloumns_dict):
                sql_replace = sql_replace + 'primary key' + r'(%s)' % Coloumns_dict['primary_key']
            j += 1

        sql = r'CREATE TABLE IF NOT EXISTS %s(%s)'% (self.Table_name, sql_replace)
        cursor = conn.cursor()
        cursor.execute(sql)
        print('成功创建%s表' % self.Table_name)

    def insert_unduplicated_dataframe(self, df, conn, primary_key):
        cursor = conn.cursor()
        # 自身去重
        df.drop_duplicates(primary_key, keep='last', inplace=True)

        # 创建临时表temp
        df.to_sql('temp', conn, if_exists='replace', index=False)

        # 创建合并dataframe的sql
        sql_1 = r''
        for key in primary_key:
            sql_1 = sql_1 + ' AND ' + r'a."%s" = b."%s"' % (key, key)
        sql_1 = sql_1.replace('AND', '', 1)

        sql = r'INSERT INTO %s SELECT * FROM ' \
              r'(SELECT a.* FROM temp a LEFT OUTER JOIN %s b ON (%s) WHERE b.%s IS NULL)' \
              % (self.Table_name, self.Table_name, sql_1, primary_key[1])

        cursor.execute(sql)

        # 删去temp表
        sql_delet = r'DROP TABLE IF EXISTS temp'
        cursor.execute(sql_delet)
        print('%s表数据入库成功' % self.Table_name)

    def max_date_query(self, conn, date_columns, **kwargs):
        if 'condition_sql' in kwargs.keys():
            sql = r'SELECT * FROM %s WHERE %s ORDER BY %s desc limit 1' % (self.Table_name, kwargs['condition_sql'], 'date')
        else:
            sql = r'SELECT * FROM %s ORDER BY %s desc limit 1' % (self.Table_name, date_columns)
        df = pd.read_sql(sql, conn)
        if len(df) == 0:
            return 20170103
        else:
            return df[date_columns].max()


if __name__ == '__main__':
    # %% 启示和终止时间的日期
    start_date = '20170103'
    end_date = '20200513'

    # %% 期货基础信息
    info_data = future_info_data(end_date=end_date, start_date=start_date)
    df_info_data = info_data.info_data_reptile()

    # %% 入库测试
    df_1 = df_info_data.copy()
    df_1.iloc[2408, 1] = 'f'
    primary_key = ['code', 'exchange']
    DB_name = r'./DB_file/future_day_data.db'
    Table_name = r'info_data'
    conn = sqlite3.connect(DB_name)
    info_data_insert = Work_DB(DB_name, Table_name)
    info_data_insert.insert_unduplicated_dataframe(df_1, conn=conn, primary_key=primary_key)
    conn.commit()
    conn.close()








